package com.example.administrator.DB;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.example.administrator.VO.UserInfo;
import com.example.administrator.VO.UserPoint;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by ykbboom on 2017/1/2.
 * 封装访问数据库的接口
 */

public class DBManager {
    private DBHelper helper;
    private SQLiteDatabase db;
    public DBManager(Context context) {
        helper = new DBHelper(context,DBHelper.DatabaseName,null,DBHelper.DatabaseVersion);
        //因为getWritableDatabase内部调用了mContext.openOrCreateDatabase(mName, 0, mFactory);
        //所以要确保context已初始化,我们可以把实例化DBManager的步骤放在Activity的onCreate里
        db = helper.getWritableDatabase();

    }

    /**
     * 在用户信息表和用户得分表插入初始化数据
     * @return NULL
     */
    public void addUser(UserInfo userInfo) {
        db.beginTransaction();  //开始事务
        try {

            db.execSQL("INSERT INTO user_info VALUES(?,?,?,?,?)", new Object[]{userInfo.get_id(),
                    userInfo.getBody_num(),userInfo.getMoney_num(),userInfo.getSign_in_time(),userInfo.getUser_name()});
            db.setTransactionSuccessful();  //设置事务成功完成
        } finally {
            db.endTransaction();    //结束事务
        }
    }

     /**
     * 在用户得分表插入数据
     * @return NULL
     */
    public void addUserPoint(UserPoint userPoint) {
        db.beginTransaction();  //开始事务
        try {

            db.execSQL("INSERT INTO user_point VALUES(null,?,?,?)", new Object[]{userPoint.getUser_id(),
                    userPoint.getTime_point(),userPoint.getStep_point()});

            db.setTransactionSuccessful();  //设置事务成功完成
        } finally {
            db.endTransaction();    //结束事务
        }
    }
    /**
     * update user's bodyNum
     */
    public void updateBodyNum(UserInfo userInfo) {
        ContentValues cv = new ContentValues();
        cv.put("body_num", userInfo.getBody_num());//设置最新的体力值
        db.update("user_info", cv, "_id = ?", new String[]{String.valueOf(userInfo.get_id())});
    }
    /**
     * update user's moneyNum
     */
    public void updateMoneyNum(UserInfo userInfo) {
        ContentValues cv = new ContentValues();
        cv.put("money_num", userInfo.getMoney_num());//设置最新的金币值
        db.update("user_info", cv, "_id = ?", new String[]{String.valueOf(userInfo.get_id())});
    }
    /**
     * update user's sign_in_time
     */
    public void updateSignInTime(int _id,long sign_in_time) {
        ContentValues cv = new ContentValues();
        cv.put("sign_in_time", sign_in_time);//设置最新的金币值
        db.update("user_info", cv, "_id = ?", new String[]{String.valueOf(_id)});
    }
    /**
     * query all users,查询所有用户信息
     * @return UserInfo
     */
    public UserInfo queryAll() {
        UserInfo userInfo = new UserInfo();
        Cursor c = queryTheCursor();
        while (c.moveToNext()) {
            userInfo.set_id(c.getInt(c.getColumnIndex("_id")));
            userInfo.setBody_num(c.getInt(c.getColumnIndex("body_num")));
            userInfo.setMoney_num(c.getInt(c.getColumnIndex("money_num")));
            userInfo.setSign_in_time(c.getLong(c.getColumnIndex("sign_in_time")));
            userInfo.setUser_name(c.getString(c.getColumnIndex("user_name")));
        }
        c.close();
        return userInfo;
    }

    /**
     * query all users,按计时得分排序，选出前五
     * @return List<UserPoint>
     */
    public List<UserPoint> queryFiveTimePoints() {
        List<UserPoint>userPoints = new ArrayList<UserPoint>();
        Cursor c = queryTopFiveTimePoints();
        while (c.moveToNext()) {
            UserPoint userPoint = new UserPoint();
            userPoint.setUser_id(c.getInt(c.getColumnIndex("user_id")));
            userPoint.setTime_point(c.getInt(c.getColumnIndex("time_point")));
            userPoints.add(userPoint);
        }
        c.close();
        return userPoints;
    }

    /**
     * query all users,按限步得分排序，选出前五
     * @return List<UserPoint>
     */
    public List<UserPoint> queryFiveStepPoints() {
        List<UserPoint>userPoints = new ArrayList<UserPoint>();
        Cursor c = queryTopFiveStepPoints();
        while (c.moveToNext()) {
            UserPoint userPoint = new UserPoint();
            userPoint.setUser_id(c.getInt(c.getColumnIndex("user_id")));
            userPoint.setStep_point(c.getInt(c.getColumnIndex("step_point")));
            userPoints.add(userPoint);
        }
        c.close();
        return userPoints;
    }
    /**
     * query all userinfos, return cursor查询所有用户信息
     * @return  Cursor
     */
    public Cursor queryTheCursor() {
        Cursor c = db.rawQuery("SELECT * FROM user_info", null);
        return c;
    }

    /**
     * query user_point top five time_point, return cursor
     * @return  Cursor
     */
    public Cursor queryTopFiveTimePoints() {
//        Cursor c = db.rawQuery("SELECT A1.* FROM user_point AS A1 " +
//                "INNER JOIN (SELECT A.user_id,A.time_point " +
//                "FROM user_point AS A " +
//                "LEFT JOIN user_point AS B " +
//                "ON A.user_id = B.user_id " +
//                "AND A.time_point < = B.time_point " +
//                "GROUP BY A.user_id,A.time_point " +
//                "HAVING COUNT(B.time_point) <= 5" +//取5条数据
//                ") AS B1 " +
//                " ON A1.user_id = B1.user_id " +
//                " AND A1.time_point = B1.time_point " +
//                "ORDER BY A1.time_point", null);
        Cursor c = db.rawQuery("SELECT * FROM user_point ORDER BY time_point DESC LIMIT 5",null);
        return c;
    }

    /**
     * query user_point top five step_point, return cursor
     * @return  Cursor
     */
    public Cursor queryTopFiveStepPoints() {
//        Cursor c = db.rawQuery("SELECT A1.* FROM user_point AS A1 " +
//                "INNER JOIN (SELECT A.user_id,A.step_point " +
//                "FROM user_point AS A " +
//                "LEFT JOIN user_point AS B " +
//                "ON A.user_id = B.user_id " +
//                "AND A.step_point <= B.step_point " +
//                "GROUP BY A.user_id,A.step_point " +
//                "HAVING COUNT(B.step_point) <= 5" +//取5条数据
//                ") AS B1 " +
//                " ON A1.user_id = B1.user_id" +
//                " AND A1.step_point = B1.step_point " +
//                "ORDER BY A1.step_point", null);
        Cursor c = db.rawQuery("SELECT * FROM user_point ORDER BY step_point DESC LIMIT 5",null);
        return c;
    }
    /**
     * close database，关闭数据库连接
     * @return NULL
     */
    public void closeDB() {
        db.close();
    }
}
